#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2014年12月13日

@author: chenyongbing
'''
import sys,os,re
current_dir = os.path.dirname(__file__)

from localConfig import baseLocalConfig



import logging


import datetime

from PyMySQL import ConnectMySQL


zhimind_host = baseLocalConfig.mysqlConfig['zhimind']['host']
zhimind_port = baseLocalConfig.mysqlConfig['zhimind']['port']
zhimind_user = baseLocalConfig.mysqlConfig['zhimind']['user']
zhimind_passwd = baseLocalConfig.mysqlConfig['zhimind']['passwd']
zhimind_db = baseLocalConfig.mysqlConfig['zhimind']['db']
myZhimind = ConnectMySQL(host=zhimind_host, port = zhimind_port,user=zhimind_user, password=zhimind_passwd, db=zhimind_db)


class ZhimindDatas(object):
    '''
    classdocs
    '''


    def __init__(self,st=None,et=None):
        '''
        Constructor
        '''
        self.st = st
        self.et = et
        self.day_query = self.__day_query__(st, et)
        self.myZhimind = myZhimind

        
    def __day_query__(self,st=None,et=None):
        if st==None:
            st = self.st
        else:
            self.st = st
        if et == None:
            et = self.et
        else:
            self.et = et
        if st==et:
            day_query = ' day ="%s" ' %(st)
        else:
            day_query = ' day between "%s" and "%s" '%(st,et)
        return day_query
        
        
    def __get_group_field__(self,groupType=4):
        if groupType==4:
            group_field = 'dgroup'
        elif groupType==2:
            group_field = 'city_group'
        elif groupType ==1:
            group_field = 'prov_group'
        elif groupType== 0:
            group_field = 'comp_group'
        else:
            group_field = 'dgroup'
        return group_field
        
        
        
    def __sql_result_to_dict__(self,rets,targetList):
        u'''sql查询内容 转换为dict'''
        datas = {}
        for ret in rets:
            group = ret[0]
            datas[int(group)] = {}
            data = {}
            save = False
            for i in range(len(targetList)):
                target = targetList[i]
                #datas[int(group)][target] = int(round(ret[i+1]))
                data[target] = int(ret[i+1])
                if int(ret[i+1])!=0:save=True
            if save:
                datas[int(group)] = data
        return datas
        
    def get_customer_info(self,tab='mac_customer_stats',groups=None,st=None,et=None,type='count',visit_freq_op='eq',visit_freq=None):
        '''visit_freq_op取值范围:
            eq:等于
            gt:大于
            lt:小于
            ge:大于等于
            le:小于等于
            ne:不等于
        '''
        
        if st==None:
            st = datetime.date.today().strftime('%Y-%m-%d')
            et = st
        elif et == None:
            et = datetime.date.today().strftime('%Y-%m-%d')
        if st>et:
            logging.error("Start Time :%s Must Litter Than End Time: %s"%(st,et))
        query = 'select sum(sum_dur),sum(visit_freq) from %s where day between "%s" and "%s"'%(tab,st,et)
        if visit_freq!=None:
            if visit_freq_op=='eq':
                query = query + ' and visit_freq=%s'%visit_freq
            elif visit_freq_op=='gt':
                query = query + ' and visit_freq>%s'%visit_freq
            elif visit_freq_op=='lt':
                query = query + ' and visit_freq<%s'%visit_freq
            elif visit_freq_op=='ge':
                query = query + ' and visit_freq>=%s'%visit_freq
            elif visit_freq_op=='le':
                query = query + ' and visit_freq<=%s'%visit_freq
            elif visit_freq_op=='ne':
                query = query + ' and visit_freq!=%s'%visit_freq
            else:
                logging.error('visit_freq_op must in (eq,gt,lt,ge,le,ne)')
                sys.exit()
        if groups!=None:
            query = query + ' and dgroup in (%s)'%','.join(groups)
        rets = myZhimind.SelectOne(query)
        if not rets:
            logging.warning("Not Find Any Records In Tabs.")
        print query
        if type=='count':
            count = rets[1]
            return count
        elif type=='avg_dur':
            avg_dur = int(rets[0]) / int(rets[1])
            return  avg_dur
    
    
    def conv_tuple_to_str(self,alist=[]):
        if len(alist)==0:
            logging.error('List Is Null.')
            return ''
        if len(alist)==1:
            return "%s"%alist[0]
        blist = []
        for a in alist:
            blist.append(str(a))
        return '"'+'","'.join(blist)+'"'
    
    
    def conv_digitalList_to_strList(self,digitalList=[]):
        if len(digitalList)==0:
            logging.error('digitalList Is Null.')
            return []
        return map(lambda group:str(group),digitalList)
        
    
    def get_flow_by_hour(self,tab='hour_stats_by_group',startTime=None,endTime=None,rtype=1,groups=[]):
        '''rtype:1:入店量;2:客流量;3:路人量
        '''
        datas = {}
        if groups==None or groups == []:return datas
        if startTime==None:
            startTime = datetime.date.today().strftime("%Y-%m-%d")
        if endTime==None:
            endTime = datetime.date.today().strftime("%Y-%m-%d")    
        query = 'select * from %s where day between "%s" and "%s"'%(tab,startTime,endTime)
        
        query = query + ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))
        query = query + ' and type="%s"'%rtype
        rets = myZhimind.SelectAll(query)
        if rets==None:rets = []
        
        tabfields = myZhimind.descTab(tab)
        
        group_indx = tabfields.index('any_group')
        day_indx = tabfields.index('day')
        for ret in rets:
            any_group = int(ret[group_indx])
            
            day = ret[day_indx]
            try:
                day = day.strftime('%Y-%m-%d')
            except:
                pass
            if any_group not in datas.keys():
                datas[any_group] = {}
            if day not in datas[any_group].keys():
                datas[any_group][day] = {}
            for field in tabfields:
                if not re.search('hour_',field):continue
                hour = int(re.sub('hour_','',field))
                count = ret[tabfields.index(field)]
                datas[any_group][day][hour] = int(count)
        return datas
    
    def get_visit_freq(self,tab='mac_customer_stats',groups=[],startTime=None,endTime=None,freqTypes={'1':'1:1','2':'2:2','3':'3:3','4':'4:4','5':'5:99'}):
        datas = {}
        if groups==[]:return datas
        
        query = 'select mac,sum(visit_freq) from %s where dgroup in (%s) and day between "%s" and "%s" group by mac'%(tab,','.join(map(lambda group:str(group),groups)),startTime,endTime)
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        
        if rets ==None:rets = []
        logging.info('Found %s Records Group By Mac.'%len(rets))
        for ret in rets:
            mac,sum_visit_freq = ret
            for freqType,freqValues in freqTypes.iteritems():
                #datas[freqType] = 0
                freq_min,freq_max = freqValues.split(':')
                if int(freq_min)<=int(sum_visit_freq)<=int(freq_max):
                    if freqType not in datas.keys():
                        datas[freqType] = 0
                    datas[freqType] = datas[freqType] +1
        for freqType in freqTypes.keys():
            if not datas.has_key(freqType):
                datas[freqType] = '-'     
        
        return datas
    
    def get_customer_count(self,tab='mac_customer_stats',groups=[],startTime=None,endTime=None,customerType=None,timeType=None,factory=None,dis_factory=None,mac_type='all'):
        '''获取入店量'''
        datas = {}
        if groups==[]:return datas
        
        if customerType=='deep':
            query = 'select dgroup,sum(deep_visit_cust_visit_cnt) from %s where dgroup in (%s)'%(tab,','.join(map(lambda group:str(group),groups)))
        elif customerType=='jump' or customerType=='out':
            query = 'select dgroup,sum(jump_out_cust_visit_cnt) from %s where dgroup in (%s)'%(tab,','.join(map(lambda group:str(group),groups)))
        else:
            query = 'select dgroup,sum(visit_freq) from %s where dgroup in (%s)'%(tab,','.join(map(lambda group:str(group),groups)))
        if startTime!=None and endTime!=None:
            query = query + ' and day between "%s" and "%s"'%(startTime,endTime)
        
        
        if timeType=='weekday' or timeType=='workday':
            query = query + ' and weekday(day) in (0,1,2,3,4)'
        elif timeType == 'weekend':
            query = query + ' and weekday(day) in (5,6)'
        
        if factory!=None:
            query+=' and factory="%s"'%factory
        if dis_factory!=None:
            query+= ' and factory!="%s"'%dis_factory
        if mac_type=='local':
            query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
        elif mac_type == 'global':
            query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
        
        query = query + ' group by dgroup'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        if rets ==None:rets = []
        logging.info('Found %s Records Group By Group.'%len(rets))
        for ret in rets:
            group,sum_visit_freq = ret
            datas[int(group)] = sum_visit_freq
        for grp in groups:
            if int(grp) not in datas.keys():
                datas[int(grp)] = '-'
        return datas






    u'''
        mac_customer_stats 数据
    '''
    
    
    def __get_mac_customer_stats_tab__(self,groupType=4):
        if groupType==0:
            return 'comp_mac_customer_stats'
        elif groupType==1:
            return 'prov_mac_customer_stats'
        elif groupType==2:
            return 'city_mac_customer_stats'
        else:
            return 'mac_customer_stats'
        
        
    def get_customer_cnt_with_delta_day(self,tab='mac_customer_stats',groups=[],groupType=4,delta_range=[0,100],st=None,et=None,factory=None,dis_factory=None,mac_type='all',distinct=True):
        '''获取活跃度顾客数量 or 次数'''    
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        
        if distinct ==True :
            query = 'select dgroup,count(distinct mac) from %s'%tab
        else:
            query = 'select dgroup,sum(visit_freq) from %s'%tab
        
        where_query_list = [day_query]
        
        if groups!=None and len(groups)!=0:
            where_query_list.append(' dgroup in (%s) '%(','.join(map(lambda group:str(group),groups))))
            
        if factory!=None:
            where_query_list.append(' factory="%s" '%factory)
        if dis_factory!=None:
            where_query_list.append(' factory!="%s" '%dis_factory)
        if mac_type=='local':
            where_query_list.append('  substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1')
        elif mac_type == 'global':
            where_query_list.append('  substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0')
            
        where_query_list.append(' delta_day between "%s" and "%s" '%(delta_range[0],delta_range[1]))
            
        query = query +' where %s '%(' and '.join(where_query_list)) +' group by dgroup '
        
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group,count in rets:
            datas[int(group)] = int(count)
        return datas  
        
    
    def get_customer_dur_from_mac_customer_stats_by_group(self,tab='mac_customer_stats',role=0,group=None,groups = [],groupType=4,st=None,et=None,factory=None,dis_factory=None,mac_type='all',macType=None,atype='sum'):
        u'''获取顾客 停留时长'''
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        if atype=='sum':
            query = 'select  dgroup,sum(sum_dur) from %s where %s '%(tab,day_query)
        elif atype=='avg':
            query = 'select  dgroup,sum(sum_dur)/sum(visit_freq) from %s where %s '%(tab,day_query)
        else:
            query = 'select  dgroup,sum(sum_dur) from %s where %s '%(tab,day_query)
        
        if group!=None:
            query = query + ' and dgroup="%s"'%group
        elif groups!=[] and groups!=None:
            query = query + ' and dgroup in (%s)'%(','.join(map(lambda group:str(group),groups)))
        if role==3:
            query = query + ' and first_day>="%s"'%self.st
        elif role==10:
            query = query + ' and first_day<"%s"'%self.st
        if macType==None:
            if factory!=None:
                query+=' and factory="%s"'%factory
            if dis_factory!=None:
                query+= ' and factory!="%s"'%dis_factory
            if mac_type=='local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mac_type == 'global':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
        else:
            query+= ' and mac_type="%s"'%macType
        query += ' group by dgroup'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group,count in rets:
            datas[int(group)] = int(count)
        return datas  
            
    
    def get_customer_visit_days_by_group(self,tab='mac_customer_stats',group=None,groupType=4,st=None,et=None):
        u'''从 %s 获取门店顾客到访天数'''%(tab)
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        
        query = 'select dgroup , count(distinct day) from %s where %s'%(tab,day_query)
        if group!=None:
            query = query + ' and dgroup="%s"'%group
        
        query += ' group by dgroup'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group,count in rets:
            datas[int(group)] = int(count)
        return datas
    
    
    
    def get_cross_customer_cnt(self,tab='mac_customer_stats',role=0,distinct=True,groups = [],crossGroups=[],groupType=4,st=None,et=None,factory=None,dis_factory=None,mac_type='all',macType=None):
        u'''从 %s 获取交叉顾客数'''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        if distinct==True:
            query = 'select  count(distinct mac) from %s '%(tab)
        else:
            query = 'select  sum(visit_freq) from %s '%(tab)
        
        
        query_where_list = [day_query]
        

        if role==3 and distinct==False:
            query_where_list.append('first_day>="%s"'%self.st)
            
        elif role==3 and distinct==True:
            query_where_list.append('first_day=day')
        elif role==10:
            query_where_list.append('first_day<"%s"'%self.st)
        
        if macType!=None:
            query_where_list.append('mac_type="%s"'%macType)
        else:
            if factory!=None:
                query_where_list.append('factory="%s"'%factory)
            if dis_factory!=None:
                query_where_list.append('factory!="%s"'%dis_factory)
            if mac_type=='local':
                query_where_list.append('substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1')
            elif mac_type == 'global':
                query_where_list.append('substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0')



        #groups mac count
        group_query1 = 'dgroup in (%s)'%(','.join(map(lambda group:str(group),groups)))
        query1 = query+' where ' +' and '.join(query_where_list+[group_query1])
        
        #cross_groups mac count
        group_query2 = 'dgroup in (%s)'%(','.join(map(lambda group:str(group),crossGroups)))
        query2 = query+' where ' +' and '.join(query_where_list+[group_query2])
        
        
        # 2 groups cross mac count
        group_query3 = 'dgroup in (%s) and mac in (select mac from %s where %s)'%(','.join(map(lambda group:str(group),groups)),tab,' and '.join(query_where_list+[group_query2]))
        query3 = query+' where ' +' and '.join(query_where_list+[group_query3])
        
        datas = []
        for any_query in [query1,query2,query3]:
            logging.debug(any_query)
            count = myZhimind.SelectCount(any_query)
            datas.append(count)
        return datas
    
    def get_customer_cnt_from_mac_customer_stats(self,tab='mac_customer_stats',role=0,distinct=True,groups = [],groupType=4,st=None,et=None,factory=None,dis_factory=None,mac_type='all',macType=None):
        u'''从 %s 获取顾客数'''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        if distinct==True:
            query = 'select  count(distinct mac) from %s where %s '%(tab,day_query)
        else:
            query = 'select  sum(visit_freq) from %s where %s '%(tab,day_query)

        if len(groups)!=0:
            query += ' and dgroup in (%s)'%(','.join(map(lambda group:str(group),groups)))
        
        if role==3 and distinct==False:
            query = query + ' and first_day>="%s"'%self.st
        elif role==3 and distinct==True:
            query = query + ' and first_day=day'
        elif role==10:
            query = query + ' and first_day<"%s"'%self.st
        
        if macType!=None:
            query += ' and mac_type="%s"'%macType
        else:
            if factory!=None:
                query+=' and factory="%s"'%factory
            if dis_factory!=None:
                query+= ' and factory!="%s"'%dis_factory
            if mac_type=='local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mac_type == 'global':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'

        ret = myZhimind.SelectOne(query)
        if ret!=None:
            return int(ret[0])
        else:
            return '-'
    def get_customer_cnt_from_mac_customer_stats_by_group(self,tab='mac_customer_stats',role=0,distinct=True,group=None,groups = [],groupType=4,st=None,et=None,factory=None,dis_factory=None,mac_type='all',macType=None):
        u'''从 %s 获取顾客数'''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        if distinct==True:
            query = 'select  dgroup,count(distinct mac) from %s where %s '%(tab,day_query)
        else:
            query = 'select  dgroup,sum(visit_freq) from %s where %s '%(tab,day_query)
        if group!=None:
            query = query + ' and dgroup="%s"'%group
        if len(groups)!=0:
            query += ' and dgroup in (%s)'%(','.join(map(lambda group:str(group),groups)))
        
        if role==3 and distinct==False:
            query = query + ' and first_day>="%s"'%self.st
        elif role==3 and distinct==True:
            query = query + ' and first_day=day'
        elif role==10:
            query = query + ' and first_day<"%s"'%self.st
        
        if macType!=None:
            query += ' and mac_type="%s"'%macType
        else:
            if factory!=None:
                query+=' and factory="%s"'%factory
            if dis_factory!=None:
                query+= ' and factory!="%s"'%dis_factory
            if mac_type=='local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mac_type == 'global':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
        
        query += ' group by dgroup'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group,count in rets:
            datas[int(group)] = int(count)
        return datas
    
    def get_deep_jump_customer_cnt_from_mac_customer_stats_by_group(self,tab='mac_customer_stats',group=None,groups=[],groupType=4,role=0,st=None,et=None,factory=None,dis_factory=None,mac_type='all',macType=None):
        u'''从 %s 获取深访跳出顾客数'''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        query = 'select dgroup,sum(deep_visit_cust_visit_cnt),sum(jump_out_cust_visit_cnt) from %s where %s'%(tab,day_query)
        if group!=None:
            query = query + ' and dgroup="%s"'%group
        if groups!=None and len(groups)!=0:
            query+= ' and dgroup in (%s)'%(','.join(map(lambda group:str(group) , groups)))
            
        if role==3:
            query = query + ' and first_day>="%s"'%self.st
        elif role==10:
            query = query + ' and first_day<"%s"'%self.st
        
        if macType!=None:
            query+=' and mac_type="%s"'%macType        
        else:
            if factory!=None:
                query+=' and factory="%s"'%factory
            if dis_factory!=None:
                query+= ' and factory!="%s"'%dis_factory
            if mac_type=='local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mac_type == 'global':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
            
        
        query += ' group by dgroup'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        datas2 = {}
        #print rets
        if rets!=None:
            for group , deep_visit_cust_visit_cnt,jump_out_cust_visit_cnt in rets:
                datas[int(group)] = int(deep_visit_cust_visit_cnt)
                datas2[int(group)] = int(jump_out_cust_visit_cnt)
        return datas,datas2




    def get_visit_cycle_from_mac_customer_stats(self,tab='mac_customer_stats',groups=None,groupType=4,day=None,st=None,et=None,factory=None,custType=0,dis_factory=None,mac_type='all',macType=None):
        u'''从 %s 获取 平均到访周期  ; 返回 dist ;示例:{groupid:avg_visit_cycle}'''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if day!=None:
            st = day
            et = day
        elif st!=None and et!=None:
            pass
        elif day==None:
            st = self.st 
            et = self.st
        day_query = self.__day_query__(st, et)     
        

         
        query = 'select dgroup,mac,max(day) from %s  where %s '%(tab,day_query)
        query = query + ' and first_day!=day'
        
        if macType !=None:
            query += ' and mac_type="%s"'%macType
            
            
        else:
            if factory!=None:
                query+=' and factory="%s"'%factory
            if dis_factory!=None:
                query+= ' and factory!="%s"'%dis_factory
            if mac_type=='local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mac_type == 'global':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
            

        
        if groups!=None:
            query = query + ' and dgroup in (%s)'%','.join(groups)
        query = query + ' group by dgroup,mac'
        
        
        query = '''select dgroup ,sum(delta_day)/count(*) from mac_customer_stats where (dgroup,mac,day) in (%s) group by dgroup'''%query
        

        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        #print rets
        if rets!=None:
            for group , avg_visit_cycle in rets:
                datas[int(group)] = int(avg_visit_cycle)
        return datas

    def get_avg_visit_cycle_from_mac_customer_stats(self,tab='mac_customer_stats',group=None,groupType=4,day=None,st=None,et=None,factory=None,dis_factory=None,mac_type='all',macType=None):
        u'''从 %s 获取 平均到访周期 只支持天数据 ; 返回 dist ;示例:{groupid:avg_visit_cycle}'''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if day!=None:
            st = day
            et = day
        elif st!=None and et!=None:
            pass
        elif day==None:
            st = self.st 
            et = self.st
        day_query = self.__day_query__(st, et)      
        query = 'select dgroup,sum(delta_day)/count(*) from %s  where %s '%(tab,day_query)
        query = query + ' and first_day!=day'
        
        if macType==None:
            query +=' and mac_type="%s"'%macType
        else:
            if factory!=None:
                query+=' and factory="%s"'%factory
            if dis_factory!=None:
                query+= ' and factory!="%s"'%dis_factory
            if mac_type=='local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mac_type == 'global':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
            
        
        if group==None:
            query = query + ' group by dgroup'
        else:
            query = query + ' and dgroup="%s"'%group
            
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        #print rets
        if rets!=None:
            for group , avg_visit_cycle in rets:
                datas[int(group)] = int(avg_visit_cycle)
        return datas
     
    def get_customer_total_visit_freq_from_mac_customer_stats_by_group(self,tab='mac_customer_stats',group=None,groupType=4,day=None,role=0):
        u'''从 %s 获取 到访次数'''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if day==None:
            day = self.st        
        query = 'select dgroup,sum(visit_freq) from %s  where day="%s" '%(tab,day)
        if role==3:
            query += ' and first_day>="%s"'%self.st
        elif role==10:
            query += ' and first_day<"%s"'%self.st
        if group==None:
            query = query + ' group by dgroup'
        else:
            query = query + ' and dgroup="%s"'%group
            
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        #print rets
        if rets!=None:
            for group , visit_freq in rets:
                datas[int(group)] = int(round(visit_freq,0))
        return datas       
        
    def get_same_target_from_mac_customer_stats(self,tab='mac_customer_stats',group=None,groupType=4,day=None,st=None,et=None,role=0,targetList=[],factory=None,dis_factory=None,mac_type='all',group_field_name='dgroup',macType=None):
        u'''验证 %s 指标 ： 顾客数(cust_cnt)、到访次数(cust_visit_cnt)、
            停留时长总和(sum_dur)、平均停留时长(avg_dur)、
            深访停留时长总和(deep_visit_sum_dur)、深访次数(deep_visit_cust_visit_cnt)、
            跳出停留时长总和(jump_out_cust_visit_cnt)
            停留时间段 人次 (dur_type_1_cnt,dur_type_2_cnt,dur_type_3_cnt,dur_type_4_cnt)
            
            深访 比例 (deep_visit_rate)
            跳出 比例 (jump_out_rate)
            
            '''%tab
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        if day!=None:
            st = day
            et = day
        elif st!=None and et!=None:
            pass
        elif day==None:
            st = self.st 
            et = self.et
        day_query = self.__day_query__(st, et)
        support_target_list = ['cust_cnt','cust_visit_cnt','sum_dur','avg_dur','deep_visit_sum_dur',
                          'deep_visit_cust_visit_cnt','jump_out_cust_visit_cnt','deep_visit_rate','jump_out_rate'
                          ]
        if targetList == []:
            targetList = support_target_list
        #group_field = self.__get_group_field__(groupType)
            
        query = 'select %s'%group_field_name
        if 'cust_cnt' in targetList:
            query += ',count(distinct mac) as cust_cnt'
        if 'cust_visit_cnt' in targetList:
            query += ',sum(visit_freq) as cust_visit_cnt'
        if 'sum_dur' in targetList:
            query +=',sum(sum_dur) as sum_dur'
        if 'avg_dur' in targetList:
            query +=',sum(sum_dur)/sum(visit_freq) as avg_dur'
        if 'deep_visit_sum_dur' in targetList:
            query +=',sum(deep_visit_sum_dur) as deep_visit_sum_dur'
        if 'deep_visit_cust_visit_cnt' in targetList:
            query +=',sum(deep_visit_cust_visit_cnt) as deep_visit_cust_visit_cnt'
        if 'jump_out_cust_visit_cnt' in targetList:
            query +=',sum(jump_out_cust_visit_cnt) as jump_out_cust_visit_cnt'
            
        if 'deep_visit_rate' in targetList:
            query += ',round(cast(sum(deep_visit_cust_visit_cnt) as  decimal(10,2))/sum(visit_freq),3)'
        
        if 'jump_out_rate' in targetList:
            query += ',round(cast(sum(jump_out_cust_visit_cnt) as decimal(10,2))/sum(visit_freq),3)' 
        for dur_type_cnt in ['dur_type_1_cnt','dur_type_2_cnt','dur_type_3_cnt','dur_type_4_cnt']:
            if dur_type_cnt in targetList:
                query += ',sum(%s) as %s'%(dur_type_cnt,dur_type_cnt) 
        
        query+=' from %s where %s'%(tab,day_query)
        if role==3:
            query+= ' and first_day>="%s"'%st
        elif role==10:
            query+= ' and first_day<"%s"'%st
        
        
        if macType==None:
            query += ' and mac_type="%s"'%macType
                
        else:
        
            if factory!=None:
                query+=' and factory="%s"'%factory
            if dis_factory!=None:
                query+= ' and factory!="%s"'%dis_factory
            if mac_type=='local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mac_type == 'global':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
            
        
        if group==None:
            query += ' group by %s'%group_field_name
        else:
            query += ' and %s = %s'%(group_field_name,group)
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for ret in rets:
            group = ret[0]
            datas[int(group)] = {}
            for i in range(len(targetList)):
                target = targetList[i]
                #datas[int(group)][target] = int(round(ret[i+1]))
                if re.search('cnt|dur',target):
                    datas[int(group)][target] = int(ret[i+1])
                else:
                    datas[int(group)][target] = float(ret[i+1])
        return datas
    
    
    def get_customer_visit_freq_scatter(self,tab='mac_customer_stats',st=None,et=None,groups=[],groupType=4,role=0,factory=None,dis_factory=None,mac_type='all',macType=None,rules={1:[1,1],2:[2,2],3:[3,5],4:[5,None]}):
        u'''
            顾客 来访次数分布
        '''
        tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        query = 'select dgroup,mac,sum(visit_freq) from %s'%tab
        
        query_where_list = []
        
        day_query = self.__day_query__(st, et)
        query_where_list.append(day_query)
        
        if groups!=None and len(groups)!=0:
            group_query = ' dgroup in (%s) '%(','.join(map(lambda group:str(group) , groups)))
            query_where_list.append(group_query)
            
        if macType!=None:
            query_where_list.append(' mac_type="%s" '%macType)
        else:
            if factory!=None:
                query_where_list.append(' factory ="%s"'%factory)
            if dis_factory!=None:
                query_where_list.append(' factory !="%s"'%dis_factory)
            if mac_type=='local':
                query_where_list.append( ' substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1 ')
            elif mac_type == 'global':
                query_where_list.append( ' substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0 ')
            
        if role==3:
            query_where_list.append(' first_day>="%s" '%st)
        elif role==10:
            query_where_list.append(' first_day<"%s" '%st)
        
        query += ' where %s group by dgroup,mac order by dgroup'%(' and '.join(query_where_list))
            
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group ,mac, visit_freq in rets:
            group = int(group)
            if group not in datas.keys():
                datas[group] = {}
            for key,num_range in rules.items():
                min_num , max_num = num_range
                if (min_num==None and int(visit_freq)<=max_num) or (max_num==None and int(visit_freq)>=min_num) or (int(visit_freq)>=min_num and int(visit_freq)<=max_num):
                    gk = key
                    break
            if gk not in datas[group].keys():
                datas[group][gk] = 0
            
            datas[group][gk]+=1
        for key in rules.keys():
            if key not in datas[group].keys():
                datas[group][key] = 0
        return datas
    
    
    def get_customer_dur_scatter_with_visit_freq(self,tab='mac_customer_stats',st=None,et=None,role=0,groups=[],groupType=4,factory=None,dis_factory=None,mac_type='all',macType=None,rules={1:[1,1],2:[2,2],3:[3,5],4:[5,None]}):
        u'''
            顾客 来访次数驻店时长分布
        '''
        #tab =self.__get_mac_customer_stats_tab__(groupType=groupType)
        groupField = self.__get_group_field__(groupType)
        query = 'select %s,mac,sum(visit_freq),sum(dur_type_1_cnt),sum(dur_type_2_cnt),sum(dur_type_3_cnt),sum(dur_type_4_cnt) from %s'%(groupField,tab)
        
        query_where_list = []
        
        day_query = self.__day_query__(st, et)
        query_where_list.append(day_query)
        
        if groups!=None and len(groups)!=0:
            group_query = ' %s in (%s) '%(groupField,','.join(map(lambda group:str(group) , groups)))
            query_where_list.append(group_query)
        if factory!=None:
            query_where_list.append(' factory ="%s"'%factory)
        if dis_factory!=None:
            query_where_list.append(' factory !="%s"'%dis_factory)
        if mac_type=='local':
            query_where_list.append( ' substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1 ')
        elif mac_type == 'global':
            query_where_list.append( ' substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0 ')
        
        
        if role==3:
            query_where_list.append(' first_day>="%s" '%st)
        elif role==10:
            query_where_list.append(' first_day<"%s" '%st)
        
        query += ' where %s group by %s,mac order by %s'%(' and '.join(query_where_list),groupField,groupField)
            
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group ,mac, visit_freq,dur_type_1_cnt,dur_type_2_cnt,dur_type_3_cnt,dur_type_4_cnt in rets:
            group = int(group)
            if group not in datas.keys():
                datas[group] = {}
            for key,num_range in rules.items():
                min_num , max_num = num_range
                if (min_num==None and int(visit_freq)<=max_num) or (max_num==None and int(visit_freq)>=min_num) or (int(visit_freq)>=min_num and int(visit_freq)<=max_num):
                    gk = key
                    break
            if gk not in datas[group].keys():
                datas[group][gk] = {1:0,2:0,3:0,4:0}
            
            datas[group][gk][1]+=int(dur_type_1_cnt)
            datas[group][gk][2]+=int(dur_type_2_cnt)
            datas[group][gk][3]+=int(dur_type_3_cnt)
            datas[group][gk][4]+=int(dur_type_4_cnt)
        for key in rules.keys():
            for group in groups:
                group = int(group)
                if group not in datas.keys():datas[group] = {}
                if key not in datas[group].keys():
                    datas[group][key] = {1:0,2:0,3:0,4:0}
        return datas
 
    
 
 
    
    u'''customer_type_stats 数据'''

    def get_customer_dist_cnt_from_customer_type_stats_by_group(self,tab='customer_type_stats',timeType='day',role=0,group=None,groups=[],groupType=4,st=None,et=None):
        u'''从 %s 获取 顾客数量 '''%tab
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        timeType =self.__change_timeType__(timeType)
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group,sum(cust_cnt) from %s where %s and cust_type="%s" and group_type=%s'%(tab,day_query,role,groupType)
        if group!=None:
            query = query + ' and any_group="%s"'%group
        if groups!=None and len(groups)!=0:
            query+= ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))
            
        query  += ' group by any_group'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group,count in rets:
            datas[int(group)] = int(count)
        return datas
    
    def get_deep_jump_customer_cnt_from_customer_type_stats_by_group(self,tab='customer_type_stats',timeType='day',group=None,groupType=4,role=0,st=None,et=None):
        u'''从 %s 获取深访跳出顾客数'''%tab
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group,sum(deep_visit_cust_visit_cnt),sum(jump_out_cust_visit_cnt) from %s where %s and group_type=%s'%(tab,day_query,groupType)
        if group!=None:
            query = query + ' and any_group="%s"'%group
        query = query + ' and cust_type="%s"'%role
        

        
        query += ' group by any_group'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        datas2 = {}
        #print rets
        if rets!=None:
            for group , deep_visit_cust_visit_cnt,jump_out_cust_visit_cnt in rets:
                datas[int(group)] = int(deep_visit_cust_visit_cnt)
                datas2[int(group)] = int(jump_out_cust_visit_cnt)
        return datas,datas2
        
        
        
    def get_active_customer_cnt_from_customer_type_stats_by_group(self,tab='customer_type_stats',timeType='day',group=None,groups=[],groupType=4,role=None,st=None,et=None):
        u'''获取 %s 活跃度顾客'''%tab
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group,sum(cust_cnt) from %s where %s and group_type=%s'%(tab,day_query,groupType)
        if group!=None:
            query = query + ' and any_group="%s"'%group
        
        if groups!=None and groups!=[]:
            query += ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))
        if role==None:
            query = query + ' and cust_type in (11,12,13,14)'
        else:
            query = query + ' and cust_type="%s"'%role
        query += ' group by any_group'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for group,count in rets:
            datas[int(group)] = int(count)
        for group in groups:
            if int(group) not in datas.keys():
                datas[int(group)] = 0
        return datas
    
    
    def __change_timeType__(self,timeType=1):
        if timeType == 1:return 'day'
        if timeType == 2 : return 'week'
        if timeType == 3 : return 'month'
        if timeType == 4 : return 'quarter'
        return timeType
    def get_same_target_from_customer_type_stats(self,tab='customer_type_stats',group=None,groups=[],groupType=4,timeType='day',day=None,st=None,et=None,role=0,targetList=[]):
        u'''验证 %s 指标 ： 顾客数(cust_cnt)、到访次数(cust_visit_cnt)、
            停留时长总和(sum_dur)、平均停留时长(avg_dur)、
            深访停留时长总和(deep_visit_sum_dur)、深访次数(deep_visit_cust_visit_cnt)、
            跳出停留时长总和(jump_out_cust_visit_cnt)'''%tab
        
        tab = self.__change_timeType__(timeType)+'_'+tab
        
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        elif day==None:
            day_query = self.__day_query__(st=day) 
        support_target_list = ['cust_cnt','cust_visit_cnt','sum_dur','avg_dur','deep_visit_sum_dur',
                          'deep_visit_cust_visit_cnt','jump_out_cust_visit_cnt','deep_visit_rate','jump_out_rate'
                          ]
        if targetList == []:
            targetList = support_target_list
        query = 'select any_group,%s'%(','.join(targetList))
        
        query+=' from %s where %s  and cust_type="%s" and group_type=%s'%(tab,day_query,role,groupType)
        if groups!=None and len(groups)!=0:
            query += ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))
#         if group==None:
#             query += ' group by any_group'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        for ret in rets:
            group = ret[0]
            datas[int(group)] = {}
            for i in range(len(targetList)):
                target = targetList[i]
                if re.search('cnt|dur',target):
                    datas[int(group)][target] = int(round(ret[i+1]))
                else:
                    datas[int(group)][target] = float(ret[i+1])
        return datas    
        
    def get_avg_visit_cycle_from_customer_type_stats(self,tab='customer_type_stats',timeType='day',group=None,groups=[],groupType=4,day=None,custType=0):
        u'''从 %s 获取 平均到访周期 只支持天数据 ; 返回 dist ;示例:{groupid:avg_visit_cycle}'''%tab
        timeType = self.__change_timeType__(timeType)
        tab = self.__change_timeType__(timeType)+'_'+tab
        if day==None:
            day = self.st 
        query = 'select any_group,avg_visit_cycle from %s where day="%s" and cust_type="%s" and group_type=%s'%(tab,day,custType,groupType)
        
        if group!=None:
            query = query + ' and any_group="%s"'%group
        if groups !=None and len(groups)!=0:
            query+= ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        #print rets
        datas = {}
        if rets!=None:
            for group , avg_visit_cycle in rets:
                datas[int(group)] = int(avg_visit_cycle)
        
        return datas
        
        

    u'''
        day_rank_stats 表格数据
    '''
    
    def get_shop_open_days(self,tab='customer_type_stats',timeType='day',group=None,groupType=4,st=None,et=None,role=0):
        u'''获取门店 开张天数 By customer Type'''
        if timeType == 'day' or timeType ==1:return 1
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
            
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select sum(avl_days) from %s'%(tab)
        query_where_list = [' cust_type="%s"'%role]
        if group!=None:
            query_where_list.append(' any_group="%s"'%group)
        if st!=None and et!=None:
            query_where_list.append(' day between "%s" and "%s"'%(st,et))
        if len(query_where_list)!=0:
            query =query +' where ' +' and '.join(query_where_list)
        logging.debug(query)
        
        ret = myZhimind.SelectOne(query)
        days = int(ret[0])
        return days
        
        
        
    def get_customer_dist_cnt_from_rank_stats_by_group(self,tab='rank_stats',timeType='day',group=None,groupType=4,st=None,et=None):
        u'''从 %s 获取 顾客数量 '''%tab
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group,sum(new_cust),sum(old_cust) from %s where %s and group_type=%s'%(tab,day_query,groupType)
        if group!=None:
            query = query + ' and any_group="%s"'%group
        query += ' group by any_group'
        datas = {}
        datas2 = {}
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        for group,new_cust,old_cust in rets:
            datas[int(group)] = int(new_cust)
            datas2[int(group)] = int(old_cust)
        return datas,datas2

    def get_walkby_cnt_from_rank_stats_by_group(self,tab='rank_stats',timeType='day',group=None,groupType=4,st=None,et=None):
        u'''从 %s 获取 路人 '''%tab
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group,sum(walkby_cnt) from %s where %s and group_type=%s'%(tab,day_query,groupType)
        if group!=None:
            query+=' and any_group="%s"'%(group)
        query+= ' group by any_group'
        logging.debug(query)
        
        datas = {}
        rets = myZhimind.SelectAll(query)
        for group,walkby_cnt in rets:
            datas[int(group)] = int(walkby_cnt)
    
        return datas
        
    
    def get_one_target_from_rank_stats_by_group(self,tab='rank_stats',timeType='day',group=None,groups=[],groupType=4,st=None,et=None,target=None):
        u'''从 %s 获取 指标'''%(tab)
        if target==None:return {}
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group ,sum(%s) from %s where %s and group_type="%s"'%(target,tab,day_query,groupType)
        if group!=None:
            query+= ' and any_group="%s"'%group
        if groups!=None and groups!=[]:
            query += ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))

        query+=' group by any_group'
        
        logging.debug(query)
        datas = {}
        rets = myZhimind.SelectAll(query)
        for group,value in rets:
            datas[int(group)] = int(value)
        return datas
    def get_target_from_rank_stats(self,tab='rank_stats',timeType='day',groups=[],groupType=4,st=None,et=None,targetList=[]):
        u'''从 %s 获取 指标'''%(tab)
        if targetList==[]:
            targetList = ['ent_cust_rate',
                          'ent_cust_cnt',
                          'visit_cnt',
                          'old_cust_rate',
                          'new_cust',
                          'new_visit_rate',
                          'old_cust',
                          'old_visit_rate',
                          'ent_cust_dur',
                          'old_cust_visit_freq',
                          'new_visit'
                          ]
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select %s from %s where %s and group_type="%s"'%(','.join(targetList),tab,day_query,groupType)
        if groups!=None and groups!=[]:
            query += ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))
        
        logging.debug(query)
        datas = {}
        ret = myZhimind.SelectOne(query)
        if ret!=None:
            return ret
        else:
            return ['-']*len(targetList)
        
    def get_target_from_rank_stats_by_group(self,tab='rank_stats',timeType='day',group=None,groups=[],groupType=4,st=None,et=None,targetList=[]):
        u'''从 %s 获取 指标'''%(tab)
        if targetList==[]:
            targetList = ['ent_cust_rate',
                          'ent_cust_cnt',
                          'visit_cnt',
                          'old_cust_rate',
                          'new_cust',
                          'new_visit_rate',
                          'old_cust',
                          'old_visit_rate',
                          'ent_cust_dur',
                          'old_cust_visit_freq',
                          'new_visit'
                          ]
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group ,%s from %s where %s and group_type="%s"'%(','.join(targetList),tab,day_query,groupType)
        if group!=None:
            query+= ' and any_group="%s"'%group
        if groups!=None and groups!=[]:
            query += ' and any_group in (%s)'%(','.join(map(lambda group:str(group),groups)))
        query+=' group by any_group'
        
        logging.debug(query)
        datas = {}
        rets = myZhimind.SelectAll(query)
        for ret in rets:
            group = int(ret[0])
            data = {}
            i=0
            for target in targetList:
                i+=1
                data[target] = ret[i]
            datas[group] = data
            
        return datas
            
    
    def get_dist_visit_cnt_from_rank_stats_by_group(self,tab='rank_stats',timeType='day',group=None,groupType=4,st=None,et=None):   
        u'''从 %s 获取 路人 '''%tab
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        tab = self.__change_timeType__(timeType)+'_'+tab
        query = 'select any_group,dist_visit_cnt from %s where %s and group_type=%s'%(tab,day_query,groupType)
        if group!=None:
            query+=' and any_group="%s"'%(group)
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        #print rets
        datas = {}
        if rets!=None:
            for group , dist_visit_cnt in rets:
                datas[int(group)] = int(dist_visit_cnt)
        return datas 
            

    u'''
        mac_walkby_stats 路人表
    '''
    
    def get_walkby_cnt_from_mac_walkby_stats_by_group(self,tab='mac_walkby_stats',group=None,groups=[],groupType=4,st=None,et=None,distinct=True,macType=None):
        u'''获取 路人 人次 人数'''
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        group_field = self.__get_group_field__(groupType)
        if distinct:
            query = 'select %s,count(*) from %s'%(group_field,tab)
        else:
            query = 'select %s,sum(visit_freq) from %s'%(group_field,tab)
        
        query = query + ' where %s'%day_query
        if group!=None:
            query += ' and %s="%s"'%(group_field,group)
        if groups!=None and groups!=[]:
            query += ' and %s in (%s)'%(group_field,','.join(map(lambda group:str(group),groups)))
        if macType!=None:
            query+=' and mac_type="%s"'%macType
            
        query+=' group by %s'%group_field
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        #print rets
        datas = {}
        if rets!=None:
            for group , visit_cnt in rets:
                datas[int(group)] = int(visit_cnt)
        return datas 
        
        
    
    
    
        
    u'''
        mac_customer_stats \ mac_walkby_stats 
    '''
    def get_visit_cnt_by_group(self,group=None,st=None,et=None,groupType=4,distinct=True,factory=None,dis_factory=None,mac_type='all'):
        '''客流量'''
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        group_field = self.__get_group_field__(groupType)
        query_customer = 'select dgroup,city_group,prov_group,comp_group,mac,visit_freq from mac_customer_stats where %s'%day_query
        if group!=None:
            query_customer += ' and %s="%s"'%(group_field,group)
        query_walkby = 'select dgroup,city_group,prov_group,comp_group,mac,visit_freq from mac_walkby_stats where %s'%day_query
        if group!=None:
            query_walkby += ' and %s="%s"'%(group_field,group)
        
        
        if distinct==True:
            query = 'select %s ,count(distinct mac) from (%s union all  %s) as mac_all '%(group_field,query_customer,query_walkby)
        else:
            query = 'select %s ,sum(visit_freq) from (%s union all %s) as mac_all '%(group_field,query_customer,query_walkby)
        
        if factory!=None:
            query+= ' where left(mac,8) in (select mac from mac_factory where factory_en="%s")'%factory
        elif dis_factory!=None:
            query+= ' where left(mac,8) in (select mac from mac_factory where factory_en!="%s")'%dis_factory
        
        if factory==None and dis_factory==None and mac_type!='all':
            if mac_type == 'local':mac_type_args = 1
            else:mac_type_args = 0
            query+= ' where substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=%s'%mac_type_args
        elif mac_type!='all':
            if mac_type == 'local':mac_type_args = 1
            else:mac_type_args = 0
            query+= ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=%s'%mac_type_args
        query += ' group by %s'%group_field
        
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        #print rets
        datas = {}
        if rets!=None:
            for group , macs in rets:
                datas[int(group)] = int(macs)
        return datas
        
        
        
    def get_mac_from_customer_and_walkby(self,groups=[],st=None,et=None,groupType=4,macType=None):
        '''获取mac地址'''
        macs = []
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        group_field = self.__get_group_field__(groupType)
        
        query_where_list = [day_query]
        
        if len(groups)!=0:
            query_where_list.append('%s in (%s)'%(group_field,','.join(map(lambda group:str(group),groups))))
        
        if macType!=None:
            query_where_list.append('mac_type="%s"'%macType)
        
        customer_query = 'select distinct mac from mac_customer_stats'
        walkby_query = 'select distinct mac from mac_walkby_stats'
        
        
        query = customer_query + ' where ' + ' and '.join(query_where_list) + ' union all ' + walkby_query + ' where '+' and '.join(query_where_list)
        
        
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        if rets==None:
            return macs
        
        for ret in rets:
            macs.append(ret[0])
        return list(set(macs))
        
        
        
        
        
        
    def get_visit_freq_by_group(self,group=None,st=None,et=None,groupType=4):
        u'''查询 平均到访次数'''
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        group_field = self.__get_group_field__(groupType)
        query_customer = 'select dgroup,city_group,prov_group,comp_group,mac,visit_freq from mac_customer_stats where %s'%day_query
        if group!=None:
            query_customer += ' and %s="%s"'%(group_field,group)
        query_walkby = 'select dgroup,city_group,prov_group,comp_group,mac,visit_freq from mac_walkby_stats where %s'%day_query
        if group!=None:
            query_walkby += ' and %s="%s"'%(group_field,group)
        
        
        query = 'select %s ,sum(visit_freq)/count(distinct mac) from (%s union all %s) as mac_all group by %s'%(group_field,query_customer,query_walkby,group_field)
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        #print rets
        datas = {}
        if rets!=None:
            for group , macs in rets:
                datas[int(group)] = int(macs)
        return datas 
    
        

        
    u'''
        hour_stats_by_group 
    '''    
    def __get_field_by_num__(self,field_top = 'hour_',min_idx=0,max_idx=23):
        fields = []
        for idx in range(min_idx,max_idx-min_idx+1):
            fields.append(field_top+str(idx))
        return fields
    def get_count_from_hour_stats_by_group(self,tab='hour_stats_by_group',group=None,groupType=4,day=None,roleType=1,min_idx=0,max_idx=23):
        u'''
            roleType:   1  入店量
                        2    客流量
                        3    路人量
        '''
        if day==None:
            day = self.st
        hour_fields = self.__get_field_by_num__(field_top='hour_', min_idx=min_idx, max_idx=max_idx)
        query = 'select any_group,sum('+'+'.join(hour_fields)+') from %s where day="%s" and type="%s"'%(tab,day,roleType)
        if group!=None:
            query += ' and any_group="%s"'%group
        query += ' group by any_group'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        if rets!=None:
            for group , count in rets:
                if count==None:count = 0
                datas[int(group)] = int(count)
        return datas
            
        
    
    u'''
    avg_day_stats_by_group
    '''
    def get_count_from_avg_day_stats_by_group(self,tab='avg_day_stats_by_group',group=None,day=None,roleType=1):
        u'''1:入店量；2:客流量;3:路人量;13:新顾客人数;14:新客流人数;15:重复顾客人数;16;重复客流人数'''
        if day==None:
            day = self.st
        query = 'select any_group,value from %s where day="%s" and  type="%s"'%(tab,day,roleType)
        if group!=None:
            query += ' and any_group="%s"'%group
        #query = query + ' group by any_group'
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = {}
        if rets!=None:
            for group , count in rets:
                if count==None:count = 0
                
                datas[int(group)] =int(round(float(count),2))
        return datas
    
    
    
    u'''
        mac_employee
    '''
    def get_employees(self,tab='mac_employee',mtype=None,group=None,day=None):
        employees = []
        query = 'select lower(mac) from %s'%(tab)
        if mtype!=None:
            query = query + ' where type="%s"'%mtype
        if group!=None and re.search('where',query):
            query = query + ' and dgroup="%s"'%group
        elif group!=None:
            query = query + ' where dgroup="%s"'%group
        if mtype!=1 and day!=None and re.search('where',query):
            query = query + ' and day="%s"'%day
        elif mtype!=1 and day!=None:
            query = query + ' where day="%s"'%day
        logging.debug(query)
        rets = myZhimind.SelectAll(query)
        datas = []
        if rets!=None:
            for mac in rets:
                datas.append(mac[0])
        return datas
    
    def get_groups_from_employee(self,tab='mac_employee',mtype=None):
        query = 'select distinct dgroup from %s'%tab
        if mtype!=None:
            query += ' where type="%s"'%mtype
        rets = myZhimind.SelectAll(query)
        groups = []
        if rets!=None:
            for gid in rets:
                groups.append(int(gid[0]))
        return groups
    
    
    
    '''company config'''
    def get_company_config(self,tab='company_config',day=None):
        query = 'select config_text from %s'%(tab)
        if day != None:
            query +=' where create_time="%s"'%day
        ret = myZhimind.SelectOne(query)
        if ret!=None:
            return ret[0]
        else:
            return None
    
    
    
    '''customer_access_stats'''
    def get_customer_access_stats(self,tab='customer_access_stats',comp_group=None,st=None,et=None):
        '''获取 顾客访问信息'''
        if st!=None and et!=None:
            day_query = self.__day_query__(st, et)
        else:
            day_query = self.day_query
        
        query = 'select comp_group,mac,mobile,mobile_addr,first_group,first_time,first_time,last_time,visit_count from %s'%tab
        query_list = []
        if comp_group!=None:
            query_list.append('comp_group="%s"'%comp_group)
        
        rets = myZhimind.SelectAll(query)
        return rets
    
    ''''''
    
    '''company_config'''
    def select_company_config(self,tab='company_config',day=None):
        if day==None:
            day = datetime.date.today().strftime('%Y-%m-%d')
        query = 'select config_text from %s where create_time<="%s" order by create_time desc limit 1'%(tab,day)
    
        logging.debug(query)
        ret = myZhimind.SelectOne(query)
        if ret==None:
            return '<root></root>'
        return  ret[0]
    
    
    
        
    
myZhimindDatas = ZhimindDatas()   
if __name__ == '__main__':
    
    groups = ['32010944', '32011158']
    #print myZhimindDatas.get_customer_info(groups=None,st="2014-12-01",type='avg_dur', visit_freq_op='gt', visit_freq=0)
    #myZhimindDatas.get_flow_by_hour(startTime='2014-12-01', endTime='2015-03-01', groups=groups)
    myZhimindDatas.get_visit_cycle_from_mac_customer_stats(groups=groups)
    